﻿CREATE PROCEDURE [dbo].[proc_Receipt_Getlist_G]
	(
		@CompanyId int,
		@SWId int,--发票状态
		@Stext nvarchar(50),
		@StartDate nvarchar(10),
		@EndDate nvarchar(10),
		@StartIndex int,
		@EndIndex int,
		@SEName nvarchar(50),
		@RID_S varchar(50),
		@RID_E varchar(50),
		@PaymentTypeId int,
		@BankId int,
		@checkxj bit,
		@rblbank INT
	)
AS
Begin

declare @strSql NVARCHAR(4000)
set @strSql=''
if @CompanyId<>0
begin
	set @strSql=@strSql+' CompanyId='+Convert(varchar,@CompanyId)+' and'
end
if @SWId<>-1
begin 
	set @strSql=@strSql+' Flag='+Convert(varchar,@SWId)+' and'
end
if @Stext<>''
begin 
	set @strSql=@strSql+' CusName like ''%'+@Stext+'%'' and'
end

if @SEName<>''
begin
	set @strSql=@strSql+' SEName like ''%'+@SEName+'%'' and'
end

if @RID_S<>'' and @RID_E<>''
begin
	set @strSql=@strSql+' RID between '''+@RID_S+''' and '''+@RID_E+''' and'
end

if @RID_S<>'' and @RID_E=''
begin
	set @strSql=@strSql+' RID ='''+@RID_S+''' and'
end

IF @PaymentTypeId=1 --现金
BEGIN   --查现金时，还需要查询电汇中需要算在现金中的电汇账号	
    IF @checkxj=0
    BEGIN
    	SET @strSql=@strSql+' PaymentTypeId=1  and'
    END
    ELSE 
    	BEGIN
    		SET @strSql=@strSql+' (PaymentTypeId=1 
	                  or BankId IN (SELECT id FROM Finance_Bank fb WHERE fb.PaymentTypeId=1 and Companyid='+CONVERT(VARCHAR, @CompanyId)+')) and'
    	END
END
ELSE IF @PaymentTypeId=2 --银行
BEGIN
	IF  @BankId=0 --电汇账号选择为全部时，查询所有算在银行中的电汇金额
	BEGIN
		SET @strSql=@strSql+' BankId IN (SELECT id FROM Finance_Bank fb WHERE fb.PaymentTypeId='+CONVERT(VARCHAR, @rblbank)+' and Companyid='+CONVERT(VARCHAR, @CompanyId)+') and'
	END
	ELSE
		BEGIN--选择了电汇账号时，查询该账号下的金额
			SET @strSql=@strSql+' BankId = (select id from Finance_Bank where id='+CONVERT(VARCHAR, @BankId)+' and  PaymentTypeId='+CONVERT(VARCHAR, @rblbank)+') and'
		END
END
ELSE IF @PaymentTypeId=3 --支票
BEGIN
	SET @strSql=@strSql+' (PaymentTypeId=3 ) and'
END

set @strSql='

	Declare @sRq Datetime,@eRq Datetime
	Set @sRq = Convert(Datetime,'''+@StartDate+' 00:00:00'')
	Set @eRq = Convert(Datetime,'''+@EndDate+' 23:59:59'')

	;WITH list As(Select ROW_NUMBER() OVER (ORDER BY flag,RDate desc,LDate desc)AS Row,
	id,
		[CompanyId]
		,[CompanyName]
      ,[CusName]
      ,[OrderId]
      ,[NOE_Flag]
      ,[Je]
      ,[RDate]
      ,[Memo]
      ,[LDate]
      ,[LUserName]
      ,[LEName]
      ,[Flag]
      ,isnull([CancelDate],''1900-1-1'') as [CancelDate]
      ,[CancelUserName]
      ,[CancelEName] ,RItem ,
			RID ,
			SEName ,
			SUserName ,
			PaymentTypeId,
			PaymentTypeName,
			BankId ,
			BankName 
		From Receipt 
		Where '+@strSql+'  RDate>=@sRq And RDate<=@eRq  
	)
	
	Select *,(select count(1) from list ) AS DataCount,
	 (select sum(Je) from list ) AS Je_Sum
	From list Where Row Between '+Convert(varchar,@StartIndex)+' and '+Convert(varchar,@EndIndex)+' Order By Row
'
exec(@strSql)
print(@strSql)
End
